using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using emp.Model;
using emp.IDAL;
using System.Collections;
using emp.Utility;

namespace emp.SqlServerDAL
{
    public class CellExp:ICellExp
    {
        //public DataSet getCellExpListDS()
        //{
        //    string SQL_SELECT_CON_LIST = "SELECT * FROM Cell_Export";

        //    SqlParameter[] parms = new SqlParameter[] { };
        //    DataSet dataset = new DataSet();
        //    using (DataSet ds = SQLHelper.ExecutedataSet(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_CON_LIST, parms))
        //    {
        //        dataset = ds;
        //    }
        //    return dataset;

        //}

        //public DataSet getCellTMExport(string sqlselect)
        //{
        //    //string SQL_SELECT_CON_LIST = "select 'N' as active,a.company_id as companyid,'' as Compliance,b.contact_id as contactid,Donotemail=case" +
        //    //                            " when b.Contact_No_Email =1 then 'Y' " +
        //    //                            " when b.Contact_No_Email=0 then 'N' " +
        //    //                            "  end,Donotmail=case " +
        //    //                            "   when b.Contact_No_Mail=1 then 'Y' " +
        //    //                            "   when b.Contact_No_Mail=0 then 'N' " +
        //    //                            "   end,Donotphone=case   " +
        //    //                            "   when b.Contact_No_TM=1 then 'Y' " +
        //    //                            "   when b.Contact_No_TM=0 then 'N' " +
        //    //                            "   end,'' as InactiveReason,'' as Level,'' as Reason,'' as Status,'' as Suppressiondate,a.company_ccms_code as TM_Companyid,b.contact_ccms_id as TM_ContactId from contact b inner join company a on b.company_id=a.company_id " + sqlselect;

        //    string SQL_SELECT_CON_LIST = " Select 'N'  as Active,Company_ID as companyid,'' as Compliance,Contact_ID as contactid,Contact_No_Email as Donotemail,Contact_No_Mail as Donotmail,Contact_No_TM as Donotphone, ";
        //    SQL_SELECT_CON_LIST += " ''as InactiveReason,'' as Level,'' as Reason,'' as Status,'' as Suppressiondate,company_ccms_code as TM_Companyid,contact_ccms_id as TM_ContactId    ";
        //    SQL_SELECT_CON_LIST += "  From Company_Contact_View  a  where 1=1  and   " + sqlselect;

        //    SqlParameter[] parms = new SqlParameter[] { };
        //    DataSet dataset = new DataSet();
        //    using (DataSet ds = SQLHelper.ExecutedataSet(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_CON_LIST, parms))
        //    {
        //        dataset = ds;
        //    }
        //    return dataset;
        //}

        //public DataSet getCellCOExport(string SAP_Tactic_Code, string SAP_Tactic_Name, string sqlselect)
        //{
        //    string SQL_SELECT_CON_LIST = " Select '' as Item ,'" + SAP_Tactic_Code + "' as SAP_Tactic_Code , '" + SAP_Tactic_Name + "' as SAP_Tactic_Name , a.* from Campaign_Other_Export a where 1=1 and  " + sqlselect;


        //    SqlParameter[] parms = new SqlParameter[] { };
        //    DataSet dataset = new DataSet();
        //    using (DataSet ds = SQLHelper.ExecutedataSet(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_CON_LIST, parms))
        //    {
        //        dataset = ds;
        //    }
        //    return dataset;

        //}

        public void QueExport(int tactic_id,string sqlselect)
        {

            string SQL_SELECT_LIST = "sp_queexport_normal";
            SqlParameter[] parms = new SqlParameter[] {
					new SqlParameter("@tactic_id", SqlDbType.Int, 4),
					new SqlParameter("@sqlselect", SqlDbType.VarChar, 4000)};
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = tactic_id;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = sqlselect;
            //Execute a query to read the products
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {

                        SQLHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, SQL_SELECT_LIST, parms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        System.Console.WriteLine(err.Message);
                    }
                }
            }

        }

        public CellExpInfo getCellExpInfo(int Contact_ID)
        {

            string SQL_SELECT_DQUE_LIST = "SELECT Contact_ID,Company_ID,Event_Registered,Event_Attended,Business_Need,Budget_Approved,Executive_Commitment,Budget_Range,Actual_Budget,Initial_Interest,Other_Interest,Decision_Date,Implementation_Date,Project_Exists,Project_Scope,Server_OS,Database_Name,Current_System,Competitor,System_Integrator,Current_Environment,Business_Impact from Cell_Export where Contact_ID=" + Contact_ID;
            CellExpInfo cellexpinfo = null;
            IList talist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_DQUE_LIST, parms))
            {
                while (rdr.Read())
                {
                    cellexpinfo =
                          new CellExpInfo();
                    
                    if (!rdr.IsDBNull(0))
                    {
                        cellexpinfo.Contact_ID=rdr.GetInt32(0);
                    }
                     if (!rdr.IsDBNull(1))
                    {
                        cellexpinfo.Company_ID=rdr.GetInt32(1);
                    }
                     if (!rdr.IsDBNull(2))
                    {
                        cellexpinfo.Que1=rdr.GetString(2);
                    }
                     if (!rdr.IsDBNull(3))
                    {
                        cellexpinfo.Que2= rdr.GetString(3);
                    }
                     if (!rdr.IsDBNull(4))
                    {
                        cellexpinfo.Que3= rdr.GetString(4);
                    }
                     if (!rdr.IsDBNull(5))
                    {
                        cellexpinfo.Que4= rdr.GetString(5);
                    }
                     if (!rdr.IsDBNull(6))
                    {
                        cellexpinfo.Que5= rdr.GetString(6);
                    }
                     if (!rdr.IsDBNull(7))
                    {
                        cellexpinfo.Que6= rdr.GetString(7);
                    }
                     if (!rdr.IsDBNull(8))
                    {
                        cellexpinfo.Que7= rdr.GetString(8);
                    }
                     if (!rdr.IsDBNull(9))
                    {
                        cellexpinfo.Que8= rdr.GetString(9);
                    }
                     if (!rdr.IsDBNull(10))
                    {
                        cellexpinfo.Que9 = rdr.GetString(10);
                    }
                     if (!rdr.IsDBNull(11))
                    {
                        cellexpinfo.Que10 = rdr.GetString(11);
                    }
                     if (!rdr.IsDBNull(12))
                    {
                        cellexpinfo.Que11 = rdr.GetString(12);
                    }
                     if (!rdr.IsDBNull(13))
                    {
                        cellexpinfo.Que12 = rdr.GetString(13);
                    }
                     if (!rdr.IsDBNull(14))
                    {
                        cellexpinfo.Que13 = rdr.GetString(14);
                    }
                     if (!rdr.IsDBNull(15))
                    {
                        cellexpinfo.Que14 = rdr.GetString(15);
                    }
                     if (!rdr.IsDBNull(16))
                    {
                        cellexpinfo.Que15= rdr.GetString(16);
                    }
                     if (!rdr.IsDBNull(17))
                    {
                        cellexpinfo.Que16 = rdr.GetString(17);
                    }
                     if (!rdr.IsDBNull(18))
                    {
                        cellexpinfo.Que17 = rdr.GetString(18);
                    }
                     if (!rdr.IsDBNull(19))
                    {
                        cellexpinfo.Que18 = rdr.GetString(19);
                    }
                     if (!rdr.IsDBNull(20))
                    {
                        cellexpinfo.Que19 = rdr.GetString(20);
                    }
                     if (!rdr.IsDBNull(21))
                    {
                        cellexpinfo.Que20 = rdr.GetString(21);
                    }
                    
                   
                }
            }
            return cellexpinfo;


        }


    }
}
